package com.che.software.testato.domain.dao.jdbc.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.che.software.testato.domain.dao.IMatrixResultDAO;
import com.che.software.testato.domain.dao.jdbc.adao.AbstractDAO;
import com.che.software.testato.domain.dao.jdbc.exception.MatrixResultCreationDAOException;
import com.che.software.testato.domain.dao.jdbc.exception.MatrixResultSearchDAOException;
import com.che.software.testato.domain.dao.jdbc.exception.MatrixResultUpdateDAOException;
import com.che.software.testato.domain.entity.MatrixResult;
import com.che.software.testato.domain.entity.search.MatrixResultSearch;

/**
 * JDBC implementation of the DAO interface dedicated to the matrix results
 * management.
 * 
 * @author Clement HELIOU (clement.heliou@che-software.com).
 * @copyright Che Software.
 * @license GNU General Public License.
 * @see AbstractDAO, IMatrixResultDAO.
 * @since August, 2011.
 * 
 *        This file is part of Testato.
 * 
 *        Testato is free software: you can redistribute it and/or modify it
 *        under the terms of the GNU General Public License as published by the
 *        Free Software Foundation, either version 3 of the License, or (at your
 *        option) any later version.
 * 
 *        Testato is distributed in the hope that it will be useful, but WITHOUT
 *        ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
 *        FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
 *        for more details.
 * 
 *        You should have received a copy of the GNU General Public License
 *        along with Testato. If not, see <http://www.gnu.org/licenses/>.
 * 
 *        Testato's logo is a creation of Arrioch
 *        (http://arrioch.deviantart.com/) and it's distributed under the terms
 *        of the Creative Commons License.
 */
@Repository("matrixResultDAO")
public class MatrixResultDAO extends AbstractDAO implements IMatrixResultDAO {

	/**
	 * Constants.
	 */
	private static final Logger LOGGER = Logger.getLogger(MatrixResultDAO.class);

	/**
	 * Creates the matrix results for a given iteration assignment. The related
	 * matrix must be completed before calling this method.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param iterationId the given iteration id.
	 * @since August, 2011.
	 * @throws MatrixResultCreationDAOException if an error occurs during the
	 *         creation.
	 */
	@Override
	public void createMatrixResults(int iterationId) throws MatrixResultCreationDAOException {
		LOGGER.debug("createMatrixResults(" + iterationId + ").");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			connection.setAutoCommit(false);
			for (MatrixResult result : getQueryRunner().query(connection, "SELECT first_script AS scriptId, iteration_assignment_id AS iterationAssignmentId, ((SUM((value/( SELECT SUM(value) FROM comparisonMatrixItem WHERE iteration_assignment_id = ? AND second_script = base.second_script GROUP BY second_script ORDER BY second_script ))))/COUNT(*))::numeric(15,2) AS percentage FROM comparisonMatrixItem base WHERE iteration_assignment_id = ? GROUP BY first_script,iteration_assignment_id ORDER BY first_script ", new BeanListHandler<MatrixResult>(MatrixResult.class), new Object[] { iterationId, iterationId })) {
				getQueryRunner().update(connection, "INSERT INTO iteration_assignment_source_script(script_id, iteration_assignment_id, percentage) VALUES(?, ?, ?) ", new Object[] { result.getScriptId(), result.getIterationAssignmentId(), result.getPercentage() });
			}
			connection.commit();
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				throw new MatrixResultCreationDAOException(e1);
			}
			throw new MatrixResultCreationDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
	}

	/**
	 * Matrix result search from a bean of criterions.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param searchBean the criterions to use for the search.
	 * @return the resulting object list.
	 * @since August, 2011.
	 * @throws MatrixResultSearchDAOException if an error occurs during the
	 *         search.
	 */
	@Override
	public List<MatrixResult> searchMatrixResult(MatrixResultSearch searchBean) throws MatrixResultSearchDAOException {
		LOGGER.debug("searchMatrixResult().");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			List<Object> params = new ArrayList<Object>();
			return getQueryRunner().query(connection, getMatrixResultSearchQueryFromCriterion(searchBean, params), new BeanListHandler<MatrixResult>(MatrixResult.class), params.toArray());
		} catch (SQLException e) {
			throw new MatrixResultSearchDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
	}

	/**
	 * Updates a matrix result from his object.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param matrixResultToUpdate the matrix result object to update in the
	 *        database.
	 * @since August, 2011.
	 * @throws MatrixResultUpdateDAOException if an error occurs during the
	 *         update.
	 */
	@Override
	public void updateMatrixResult(MatrixResult matrixResultToUpdate) throws MatrixResultUpdateDAOException {
		LOGGER.debug("updateMatrixResult(" + matrixResultToUpdate.getIterationAssignmentId() + "," + matrixResultToUpdate.getScriptId() + ").");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			List<Object> params = new ArrayList<Object>();
			getQueryRunner().update(connection, getMatrixResultUpdateQueryFromCriterion(matrixResultToUpdate, params), params.toArray());
		} catch (SQLException e) {
			throw new MatrixResultUpdateDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
	}

	/**
	 * Recovery of the matrix result search query from criterion.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param searchBean the object containing the criterions.
	 * @param params the parameters list corresponding to the built query.
	 * @return the built query.
	 * @since August, 2011.
	 */
	private String getMatrixResultSearchQueryFromCriterion(MatrixResultSearch searchBean, List<Object> params) {
		LOGGER.debug("getMatrixResultSearchQueryFromCriterion().");
		setWhereClauseEnabled(false);
		StringBuilder sBuilder = new StringBuilder("SELECT script_id AS scriptId, iteration_assignment_id AS iterationAssignmentId, percentage , label AS scriptLabel FROM iteration_assignment_source_script JOIN script USING(script_id) ");
		if (null != searchBean && null != searchBean.getIterationAssignmentId() && 0 != searchBean.getIterationAssignmentId()) {
			sBuilder.append(getWhereClauseBegin());
			sBuilder.append("iteration_assignment_id = ? ");
			params.add(searchBean.getIterationAssignmentId());
		}
		if (null != searchBean && null != searchBean.getSelected()) {
			sBuilder.append(getWhereClauseBegin());
			sBuilder.append("selected = ? ");
			params.add(searchBean.getSelected());
		}
		sBuilder.append("ORDER BY script_id ");
		return sBuilder.toString();
	}

	/**
	 * Recovery of the matrix result update query from criterion.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param matrixResultToUpdate the object containing the criterions.
	 * @param params the parameters list corresponding to the built query.
	 * @return the built query.
	 * @since July, 2011.
	 */
	private String getMatrixResultUpdateQueryFromCriterion(MatrixResult matrixResultToUpdate, List<Object> params) {
		LOGGER.debug("getMatrixResultUpdateQueryFromCriterion().");
		setSetClauseEnabled(false);
		StringBuilder sBuilder = new StringBuilder("UPDATE iteration_assignment_source_script ");
		if (null != matrixResultToUpdate && null != matrixResultToUpdate.getSelected()) {
			sBuilder.append(getSetClauseBegin());
			sBuilder.append("selected = ? ");
			params.add(matrixResultToUpdate.getSelected());
		}
		sBuilder.append("WHERE script_id = ? AND iteration_assignment_id = ? ");
		params.add(matrixResultToUpdate.getScriptId());
		params.add(matrixResultToUpdate.getIterationAssignmentId());
		return sBuilder.toString();
	}
}